# import pandas as pd
#from model_zh import * 
# import time
# import cx_Oracle
# from sqlalchemy import create_engine
#from scipy.stats import norm

import pandas as pd
import time
import cx_Oracle
from sqlalchemy import create_engine
from apply_rule import *
from check_bug import * 
from data_clean import *
from decide_params import *
from diclst import * 
from GetScore import *
from model_classify import *
from score_transformer import *
from sql_connect import *  




#读取数据
def score_card(id_mark):
    print('模型开始计算')
    print('-'*15,time.strftime('%Y/%m/%d %H:%M:%S',time.localtime()),'-'*15)
    #获取数据库参数
    host,port,databases,user,password  = load_db_params('config.csv')

    #判断是跑批还是跑单条 返回索引ID
    index_id,evalate_id,batch_flag  = which_id(id_mark)              
                     
    # 数据库联接
    engine,conn = db_connect(host,port,databases,user,password)                       

    #读取数据
    dic_sql = return_sql(index_id,evalate_id)                                              
    dff = ReadSql(dic_sql['sql_dff'],engine,'特征')  
    df_w = ReadSql(dic_sql['sql_df_w'],engine,'权重')
    df_add_score = ReadSql(dic_sql['sql_df_add_score'],engine,'金综')
    dfjch = ReadSql(dic_sql['sql_dfjch'],engine,'基础')
    dfs_fill = get_config_data('dfs_fill.csv')
    df_fill = get_config_data('df_fill.csv')


    batch_id = get_batch_id(dfjch,batch_flag)                                     #如果是跑批次则获取批次id 
    if dfjch.batch_id.values[0]:                                                  #java让新加的 即单条也加batch_id
        batch_id = dfjch.batch_id.values[0]

    df = dff                                                                          #改名
    df_record = rule_record(df)                                                       #计算策略命中记录表
    df_record['batch_id'] = batch_id                                                  #加上batch_id 

    dfr = find_rule(df)                                                               #标记命中策略的企业 后边进行策略调整一票否决
    df = feature_engineing(df)                                                        #聚合apply特征 填充缺失值

    #得到模型分类结果 利用rating_id关联
    dfjch2 = get_model_type(dfjch,dic_size,dic_size_gb,lst_1,lst_0,lst_all)   
    df = pd.merge(df,dfjch2[['rating_id','model_type']],left_index = True,right_on='rating_id').set_index('rating_id') #保证特征表的rating_id在索引上 （后续要求）

    #生成分数
    dfs = pd.DataFrame()                    
    dfs_NoWeig = pd.DataFrame()
    for model_type in ['ytb','yts','ntb','nts']:
        df_mt =  df.loc[df.model_type == model_type]                               #特征切片
        df_w_mt = df_w.loc[df_w.model_type == model_type].sort_values('eng_name')  #权重切片并排序
        
        if len(df_mt) == 0:
            print('此次计算中没有{}类型客户，跳过计算'.format(model_type))         #切片为0  则无该类型跳过
            continue
        
        score_group = transform_score(df_mt,model_type,df_w_mt,df_fill)                   
        score_df = score_group[0]                                                  #有权重的分数
        df_NoWeight =  score_group[1]                                              #无权重的分数
        lst = score_group[-1]                                                      #如果报错可以看这个列表参考
           
        dfs = pd.concat([dfs,score_df])                                            #拼接有权重的分数
        dfs_NoWeig = pd.concat([dfs_NoWeig,df_NoWeight])                           #拼接乘权重前的分数
        
    if len(dfs) == 0:
        print('此次计算中客户为不评价群体')
        esg_result = dfjch[['id_number','rating_id','model_type','industry_code']]
        esg_result.columns = ['enterprise_cert_no','rating_id','model_type','industry_code']
        esg_result['esg_rating'] = 'M'
        DelSql('esg_model_rating_result',index_id,evalate_id,conn)
        ToSql(esg_result,'esg_model_rating_result',engine)
        return 0
    #获取因子分数窄表
    df_factor = get_factor_score(dfs,dic_eng_china)    
    df_factor['factor_code'] = df_factor.factor_name.map(dic_index_MapCode4)
    df_factor['batch_id'] = batch_id


    #获取指标分数窄表
    df_IndexScore = pd.DataFrame()

    df_index_score3 = get_index_score(dfs,dic_eng_china,dic_index_map43)              #聚合
    df_index_score2 = get_index_score(dfs,dic_eng_china,dic_index_map42)              #聚合

    df_IndexScore = pd.concat([df_index_score3,df_index_score2])                      #拼接
    df_IndexScore['index_code'] =  df_IndexScore.index_name.map(dic_index_MapCodeAll) #添加code
    df_IndexScore['batch_id'] = batch_id

    dfi = df_IndexScore

    #添加金综分数
    df_score,lst = add_score(dfs,df,df_add_score,dic_EngFeature_dim,dfs_fill) #金综添加小分数
    #print('列表{}中数据需要单独分析'.format(str(lst)))
    #进行策略调整
    df_esg_result = one_kill(df_score,dfr)                                                                          #一票否决策略调整
    df_esg_result['batch_id'] = batch_id

    #返回全量企业
    result = return_allcustomer(df_esg_result,dfjch2)   
    result['batch_id'] = batch_id

    #如果是跑批则计算等级
    if batch_flag == 1 :
        esg_result = get_level(result)
    else:
        esg_result = result

    dfo = esg_result.loc[~esg_result.model_type.isin(['bup2','bup3','bup1'])].copy()
    
    #在因子表与指标表上加入模型类型
    if len(df_factor) != 0:
        df_factor = pd.merge(df_factor,esg_result[['rating_id','model_type']],on = 'rating_id',how='left')    
    if len(df_IndexScore) != 0:
        df_IndexScore = pd.merge(df_IndexScore,esg_result[['rating_id','model_type']],on = 'rating_id',how='left')

    #暂时切片
    esg_result = esg_result.iloc[:10]
    df_record = df_record[:10]
    df_factor = df_factor.iloc[:10]
    df_IndexScore = df_IndexScore.iloc[:10]

    #删除中文名字
    del df_factor['factor_name']
    del df_IndexScore['index_name']



    #删除数据
    DelSql('esg_model_rule_result',index_id,evalate_id,conn)
    DelSql('esg_model_rating_result',index_id,evalate_id,conn)
    DelSql('esg_model_index_result',index_id,evalate_id,conn)
    DelSql('esg_model_factor_result',index_id,evalate_id,conn)


    #转换数据类型
    print('开始数据转换')
    dtyp_index = get_dtyp(df_IndexScore)
    dtyp_esg_result = get_dtyp(df_esg_result)
    dtyp_factor = get_dtyp(df_factor)
    dtyp_record = get_dtyp(df_record)
    print('写入数据库',time.strftime('%Y%m%d %H:%M:%S',time.loacltime()))

    #写入数据
    ToSql(esg_result,'esg_model_rating_result',engine,dtyp_index)
    ToSql(df_IndexScore,'esg_model_index_result',engine,dtyp_index)
    ToSql(df_factor,'esg_model_factor_result',engine,dtyp_factor)
    ToSql(df_record,'esg_model_rule_result',engine,dtyp_record)
